package com.company.project.utils;


import com.company.project.constants.Constants;
import com.company.project.core.exception.BusinessException;
import com.company.project.core.support.ErrorCode;
import com.company.project.model.dto.CommonExcelDto;
import net.sf.jxls.transformer.XLSTransformer;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * Excel生成类. 
 */
public class ExcelUtil {

    public static final String OFFICE_EXCEL_2003_POSTFIX = "xls";
    public static final String OFFICE_EXCEL_2010_POSTFIX = "xlsx";

    /**
     * poi生成简易excel文件，单一sheet表头多行数据
     */
    public static InputStream buildCommonExcelDocument(CommonExcelDto commonExcelDto){
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 创建Excel的工作shet,对应到一个excel文档的tab
        XSSFSheet sheet = (XSSFSheet)workbook.createSheet("sheet1");
        // 创建Excel的sheet的一行
        int rowNum = commonExcelDto.getRowNum();
        //设置表头
        List<String> headerList = commonExcelDto.getHeader();
        if(rowNum>0){
            XSSFRow title = sheet.createRow(0);

            XSSFCellStyle cellStyle=workbook.createCellStyle();
            // 字体样式
            Font fontStyle = workbook.createFont();
            // 加粗
//            fontStyle.setBold(true);
            // 字体
            fontStyle.setFontName("黑体");
            // 大小
            fontStyle.setFontHeightInPoints((short) 11);
            // 将字体样式添加到单元格样式中
            cellStyle.setFont(fontStyle);
            // 边框，居中
            cellStyle.setAlignment(HorizontalAlignment.CENTER);

            XSSFCell cell = title.createCell(0);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(commonExcelDto.getTitleName());
            // 单元格合并
            // 四个参数分别是：起始行，结束行，起始列，结束列
            sheet.addMergedRegion(new CellRangeAddress(0,rowNum-1,0,headerList.size()-1));
        }



        XSSFRow header = sheet.createRow(rowNum+1);
        XSSFRow row;
        int headerCount=0;
        for(String headerName:headerList){
            header.createCell(headerCount).setCellValue(headerName);
            headerCount++;
        }
        //填充数据
        List<List<String>> contentList = commonExcelDto.getContent();
        if(contentList != null ){
            for (int rowCount=0;rowCount< contentList.size();rowCount++ ){
                int cellCount=0;
                //空开表头
                row = sheet.createRow(rowCount+rowNum+2);
                List<String> rowContent = contentList.get(rowCount);
                for(String cellContent:rowContent){
                    row.createCell(cellCount).setCellValue(cellContent);
                    cellCount++;
                }
            }
        }
        try(ByteArrayOutputStream os = new ByteArrayOutputStream();) {
            workbook.write(os);
            byte[] content = os.toByteArray();
            return new ByteArrayInputStream(content);
        } catch (IOException e) {
            throw new BusinessException(ErrorCode.INTERNAL_SERVER_ERROR,"无法生成",e);
        }
    }

    /**
     * 根据模板生成Excel文件. 
     * @param templateFileName 模板文件. 
     * @param map 模板中存放的数据.
     * @param resultFileName 生成的文件. 
     */
    public static void createExcel(String templateFileName, Map<String,Object> map, String resultFileName){
        try {
            //创建XLSTransformer对象  
            XLSTransformer transformer = new XLSTransformer();
            //生成Excel文件
            transformer.transformXLS(templateFileName, map, resultFileName);
        } catch (Exception e) {
            throw new RuntimeException("error happens...", e);
        }
    }

    public static String getPostfix(String path) {
        if (path.contains(".")) {
            return path.substring(path.lastIndexOf(".") + 1, path.length());
        }
        return "";
    }

    public static Workbook getWorkBookFromFile(MultipartFile file) {
        if (file!=null) {
            String postfix = ExcelUtil.getPostfix(file.getOriginalFilename());
            try{
                if (ExcelUtil.OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {
                    return new HSSFWorkbook(file.getInputStream());
                } else if (ExcelUtil.OFFICE_EXCEL_2010_POSTFIX.equals(postfix)) {
                    return new XSSFWorkbook(file.getInputStream());
                } else {
                    throw new BusinessException(ErrorCode.VALID_NOT_PASS, "非excel文件");
                }
            }catch(IOException e){
                throw new BusinessException(ErrorCode.VALID_NOT_PASS, "非excel文件");
            }
        }
        return null;
    }

    /**
     *
     * @param file
     * @param titleNum title所在行数
     * @return
     */
    public static List<Object[]> readExcel(MultipartFile file, int titleNum){
        Workbook workbook = ExcelUtil.getWorkBookFromFile(file);
        if(workbook == null){
            throw new BusinessException(ErrorCode.INTERNAL_SERVER_ERROR,"无法获取文件位置");
        }
        Sheet sheet = workbook.getSheetAt(0);
        int rowStart = titleNum;
        int rowEnd = sheet.getLastRowNum();
        List<Object[]> dataList=new ArrayList<>();
        //获取抬头
        Row title = sheet.getRow(titleNum);
        int columnSize=title.getLastCellNum();
        Object[] titleObject=new Object[columnSize];
        for(int i=0;i<columnSize;i++){
            Cell cell = title.getCell(i);
            if(cell!=null){
                titleObject[i]= cell.getStringCellValue();
            }
        }
        dataList.add(titleObject);
        for(int i=rowStart+1;i<=rowEnd;i++){
            Row row = sheet.getRow(i);
            if (null == row) {
                continue;
            }
            int cellStart = row.getFirstCellNum();
            int cellEnd = row.getLastCellNum();
            Object[] valueObject=new Object[columnSize];
            for (int j=cellStart;j<cellEnd; j++){
                boolean isMerger=isMergedRegion(sheet,i,j);
                if(isMerger){
                    valueObject[0]=getMergedRegionValue(sheet,i,j);
                    break;
                }else{
                    valueObject[j]=getCellValue(row.getCell(j));
                }

            }
            dataList.add(valueObject);
        }

        return dataList;
    }


    public static Object getCellValue(Cell cell) {
        if(cell == null){
            return  "";
        }
        Object value = null;
        int cellType = cell.getCellType();
        switch (cellType) {
            //空值
            case Cell.CELL_TYPE_BLANK:
                value = "";
                break;
            //字符串
            case Cell.CELL_TYPE_STRING:
                value = cell.getStringCellValue();
                break;
            // Boolean
            case Cell.CELL_TYPE_BOOLEAN:
                value = cell.getBooleanCellValue();
                break;
            // 故障
            case Cell.CELL_TYPE_ERROR:
                value = Constants.EXCEL_ERROR_MESSAGE;
                break;
            // 数字
            case Cell.CELL_TYPE_NUMERIC:
                short dataFormat = cell.getCellStyle().getDataFormat();
                //处理自定义单元格为2017年12月30日的类型
                if(dataFormat==31){
                    value = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(cell.getNumericCellValue());
                }else{
                    //判断是否是时间
                    if (HSSFDateUtil.isCellDateFormatted(cell)){
                        value=cell.getDateCellValue();
                    }else{
                        value = cell.getNumericCellValue();
                    }
                }
                break;
            // 公式
            case HSSFCell.CELL_TYPE_FORMULA:
                try {
                    value = String.valueOf(cell.getNumericCellValue()).trim();
                } catch (IllegalStateException e) {
                    value = String.valueOf(cell.getRichStringCellValue()).trim();
                }
                break;
            default:
                value = cell.getRichStringCellValue().getString();
        }
        return value;
    }

    /**
     * 判断指定的单元格是否是合并单元格
     * @param sheet
     * @param row 行下标
     * @param column 列下标
     * @return
     */
    public static boolean isMergedRegion(Sheet sheet,int row ,int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if(row >= firstRow && row <= lastRow){
                if(column >= firstColumn && column <= lastColumn){
                    return true;
                }
            }
        }
        return false;
    }

    /**
     * 获取合并单元格的值
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    public static Object getMergedRegionValue(Sheet sheet ,int row , int column){
        int sheetMergeCount = sheet.getNumMergedRegions();

        for(int i = 0 ; i < sheetMergeCount ; i++){
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();

            if(row >= firstRow && row <= lastRow){

                if(column >= firstColumn && column <= lastColumn){
                    Row fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);
                    return getCellValue(fCell) ;
                }
            }
        }

        return null ;
    }
}